package com.um.psystem.service.exService.impl;

import com.um.psystem.entity.ExcelHeader;
import com.um.psystem.mapper.platform.PublicMapper;
import com.um.psystem.model.vo.JsonResult;
import com.um.psystem.service.exService.IExceptEventService;
import com.um.psystem.utils.EasyExcelUtils;
import com.um.psystem.utils.RTX;
import org.apache.shiro.crypto.hash.Hash;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import javax.swing.plaf.nimbus.State;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Auther: AiLen2E
 * @Date: 2021/11/2 13:59
 * @version: 1.0
 */
@Service
public class ExceptEventService implements IExceptEventService {

    @Autowired
    PublicMapper publicMapper;

    @Override
    public List<Map<String, Object>> getExceptionEventList(Map map) {
        String sqltmp = " WHERE 1=1";

        Map paramsMap = new HashMap();
        Integer page = Integer.parseInt((map.get("page") != null ? map.get("page").toString() : "0"));
        Integer rows = Integer.parseInt((map.get("rows") != null ? map.get("rows").toString() : "0"));

        if (map.containsKey("eid") && (!map.get("eid").toString().equals(""))) {
            String eid = map.get("eid").toString();
            paramsMap.put("eid", eid);
            sqltmp +=" AND eid=#{eid}";
        }

        if (map.containsKey("machineType") && (!map.get("machineType").toString().equals(""))) {
            String machineType = map.get("machineType").toString();
            paramsMap.put("machineType", machineType);
            sqltmp +=" AND machineType=#{machineType}";
//            sqltmp+=" WHERE machineType='' AND erp='' AND conDept='' AND conBillNo='' AND cusSentToName='' AND b.dept_name='' AND subm_men='' AND follow_up_people=''";
        }

        if (map.containsKey("wide_type") && (!map.get("wide_type").toString().equals(""))) {
            String wide_type = map.get("wide_type").toString();
            paramsMap.put("wide_type", wide_type);
            sqltmp += " AND wide_type=#{wide_type}";
        }

        if (map.containsKey("erp") && (!map.get("erp").toString().equals(""))) {
            String erp = map.get("erp").toString();
            paramsMap.put("erp", erp);
            sqltmp += " AND erp=#{erp}";
        }

        if (map.containsKey("conDept") && (!map.get("conDept").toString().equals(""))) {
            String conDept = map.get("conDept").toString();
            paramsMap.put("conDept", conDept);
            sqltmp += " AND conDept=#{conDept}";
        }

        if (map.containsKey("conBillNo") && (!map.get("conBillNo").toString().equals(""))) {
            String conBillNo = map.get("conBillNo").toString();
            paramsMap.put("conBillNo", conBillNo);
            sqltmp += " AND conBillNo=#{conBillNo}";
        }

        if (map.containsKey("cusSentToName") && (!map.get("cusSentToName").toString().equals(""))) {
            String cusSentToName = map.get("cusSentToName").toString();
            paramsMap.put("cusSentToName", cusSentToName);
            sqltmp += " AND cusSentToName=#{cusSentToName}";
        }

        if (map.containsKey("dept_id") && (!map.get("dept_id").toString().equals(""))) {
            String dept_id = map.get("dept_id").toString();
            paramsMap.put("dept_id", dept_id);
            sqltmp += " AND resp_dept=#{dept_id}";
        }

        if (map.containsKey("subm_men") && (!map.get("subm_men").toString().equals(""))) {
            String subm_men = map.get("subm_men").toString();
            paramsMap.put("subm_men", subm_men);
            sqltmp += " AND subm_men=#{subm_men}";
        }

        if (map.containsKey("follow_up_people") && (!map.get("follow_up_people").toString().equals(""))) {
            String follow_up_people = map.get("follow_up_people").toString();
            paramsMap.put("follow_up_people", follow_up_people);
            sqltmp += " AND follow_up_people=#{follow_up_people}";
        }

        if (map.containsKey("exception_type_id") && (!map.get("exception_type_id").toString().equals(""))) {
            String exception_type_id = map.get("exception_type_id").toString();
            paramsMap.put("exception_type_id", exception_type_id);
            sqltmp += " AND exception_type_id=#{exception_type_id}";
        }

        if (map.containsKey("state") && (!map.get("state").toString().equals(""))) {
            String state = map.get("state").toString();
            paramsMap.put("state", state);
            sqltmp += " AND state=#{state}";
        }

        if (map.containsKey("occurrence_time_start") && (!map.get("occurrence_time_start").toString().equals("")) && map.containsKey("occurrence_time_end") && (!map.get("occurrence_time_end").toString().equals(""))) {
            String occurrence_time_start = map.get("occurrence_time_start").toString();
            String occurrence_time_end = map.get("occurrence_time_end").toString();
            paramsMap.put("occurrence_time_start", occurrence_time_start);
            paramsMap.put("occurrence_time_end", occurrence_time_end);
            sqltmp += " AND occurrence_time BETWEEN #{occurrence_time_start} AND #{occurrence_time_end}";
        }

        if (map.containsKey("submit_time_start") && (!map.get("submit_time_start").toString().equals("")) && map.containsKey("submit_time_end") && (!map.get("submit_time_end").toString().equals(""))) {
            String submit_time_start = map.get("submit_time_start").toString();
            String submit_time_end = map.get("submit_time_end").toString();
            paramsMap.put("submit_time_start", submit_time_start);
            paramsMap.put("submit_time_end", submit_time_end);
            sqltmp += " AND subm_time BETWEEN #{submit_time_start} AND #{submit_time_end}";
        }

        String sql = "SELECT COUNT(*) as totals FROM ws_eng_exception_event a " +
                "JOIN ws_eng_exception_dept b ON a.resp_dept=b.id " +
                "JOIN ws_eng_exception_type c ON a.exception_type_id=c.id " +
                "JOIN uws_produceorder_confirmation d ON a.erp=d.conErpNo"+sqltmp;

        paramsMap.put("sqlStr", sql);
        int totals = 0;
        List<Map<String, Object>> list_count = publicMapper.getPublicItems(paramsMap);
        if (list_count.size() > 0) {
            totals = Integer.parseInt(list_count.get(0).get("totals").toString());
        } else {
            System.out.println("****************:" + totals);
        }

        sql = "SELECT a.*,b.dept_name,c.exception_type_name,d.conBillNo,d.machineType,d.cusSentToName,d.conDept,"+totals+" as totals FROM ws_eng_exception_event a " +
                "JOIN ws_eng_exception_dept b ON a.resp_dept=b.id " +
                "JOIN ws_eng_exception_type c ON a.exception_type_id=c.id " +
                "JOIN uws_produceorder_confirmation d ON a.erp=d.conErpNo"+sqltmp;

        if (page != 0 && rows != 0) {
            sql += " limit #{startIndex},#{rows} ";
            paramsMap.put("startIndex", (page - 1) * rows);
            paramsMap.put("rows", rows);
        }
        paramsMap.put("sqlStr", sql);
        return publicMapper.getPublicItems(paramsMap);
    }

    @Override
    public JsonResult<Integer> deleExceptionEvent(Integer id) {

        String state;
        try {
            HashMap parm = new HashMap();
            parm.put("eid",id);
            List<Map<String, Object>> list = getExceptionEventList(parm);
            if (list.size()>0)
            {
                state = list.get(0).get("state").toString();
                if (state.equals("2"))
                {
                    return JsonResult.error(403,"不可以删除已完成的异常事件");
                }
            }
            else
            {return JsonResult.error(404,"异常事件不存在"); }
        }catch (Exception e)
        {return JsonResult.error(403,"非法请求");}

        Map paramsMap = new HashMap();
        String sql = "DELETE FROM ws_eng_exception_event WHERE eid=#{eid}";
        paramsMap.put("dsqlStr",sql);
        paramsMap.put("eid",id);
        Integer updateNum = publicMapper.delItems(paramsMap);
        return JsonResult.success(updateNum);
    }

    @Override
    public JsonResult<Integer> addExceptionEvent(Map map) {
        Map paramsMap = new HashMap();

        if (map.get("erp")!=null && !map.get("erp").equals(""))
        {
            for (Object key : map.keySet())
            {
                paramsMap.put(key,map.get(key));
            }
        }
        else
        { return JsonResult.error(403,"非法请求！"); }

        String sql = "INSERT INTO ws_eng_exception_event " +
                "VALUES(NULL,#{wide_type},#{erp},#{pro_type},#{problem_description}," +
                "#{analysis_result},#{exp_proce_results},#{exception_type_id}," +
                "#{resp_dept},#{temp_disposal},#{subm_men},#{follow_up_people},#{occurrence_time}," +
                "NOW(),NULL,0)";

        paramsMap.put("isqlStr",sql);

        Integer updateNum = publicMapper.saveItems(paramsMap);
        sendRTXNoti(map.get("follow_up_people").toString());
        return JsonResult.success(updateNum);
    }

    @Override
    public JsonResult<Integer> editExceptionEvent(Map map) {
        Map paramsMap = new HashMap();

        if (map.get("eid")==null || map.get("eid").equals(""))
        { return JsonResult.error(403,"非法请求"); }
        if (map.get("erp")==null || map.get("erp").equals(""))
        { return JsonResult.error(403,"非法请求"); }

        String state;
        try {
            HashMap parm = new HashMap();
            parm.put("eid",Integer.parseInt(map.get("eid").toString()));
            List<Map<String, Object>> list = getExceptionEventList(parm);
            if (list.size()>0)
            {
                state = list.get(0).get("state").toString();
                if (state.equals("2"))
                {
                    return JsonResult.error(403,"不可以编辑已完成的异常事件");
                }
            }
            else
            {return JsonResult.error(404,"异常事件不存在"); }
        }catch (Exception e)
        {return JsonResult.error(403,"非法请求");}

        for (Object key:map.keySet())
        {
            paramsMap.put(key,map.get(key));
        }

        String sql = "UPDATE ws_eng_exception_event " +
                "SET wide_type=#{wide_type},erp=#{erp},pro_type=#{pro_type},problem_description=#{problem_description},analysis_result=#{analysis_result},exp_proce_results=#{exp_proce_results},exception_type_id=#{exception_type_id},resp_dept=#{resp_dept},temp_disposal=#{temp_disposal},subm_men=#{subm_men},follow_up_people=#{follow_up_people},occurrence_time=#{occurrence_time} " +
                "WHERE eid=#{eid}";
        paramsMap.put("usqlStr", sql);

        return JsonResult.success(publicMapper.updateItems(paramsMap));
    }

    @Override
    public List<Map<String, Object>> getExceptionEventDtlList(Map map) {
        String sqltmp = " WHERE 1=1";

        Map paramsMap = new HashMap();
        Integer page = Integer.parseInt((map.get("page") != null ? map.get("page").toString() : "0"));
        Integer rows = Integer.parseInt((map.get("rows") != null ? map.get("rows").toString() : "0"));

        if (map.containsKey("id") && (!map.get("id").toString().equals(""))) {
            String id = map.get("id").toString();
            paramsMap.put("id", id);
            sqltmp +=" AND id=#{id}";
        }

        if (map.containsKey("eid") && (!map.get("eid").toString().equals(""))) {
            String eid = map.get("eid").toString();
            paramsMap.put("eid", eid);
            sqltmp +=" AND eid=#{eid}";
        }

        String sql = "SELECT COUNT(*) as totals FROM ws_eng_exception_dtl a " +
                "JOIN ws_eng_exception_dept b on a.dept=b.id"+sqltmp;

        paramsMap.put("sqlStr", sql);
        int totals = 0;
        List<Map<String, Object>> list_count = publicMapper.getPublicItems(paramsMap);
        if (list_count.size() > 0) {
            totals = Integer.parseInt(list_count.get(0).get("totals").toString());
        } else {
            System.out.println("****************:" + totals);
        }

        sql = "SELECT a.*,b.dept_name,"+totals+" as totals FROM ws_eng_exception_dtl a " +
                "JOIN ws_eng_exception_dept b on a.dept=b.id"+sqltmp;

        if (page != 0 && rows != 0) {
            sql += " limit #{startIndex},#{rows} ";
            paramsMap.put("startIndex", (page - 1) * rows);
            paramsMap.put("rows", rows);
        }
        paramsMap.put("sqlStr", sql);
        return publicMapper.getPublicItems(paramsMap);
    }

    @Override
    public JsonResult<Integer> deleExceptionEventDtl(Integer id) {
        try {
            String state = getEventByDtlId(id).get("state").toString();
            if (state.equals("2"))
            {return JsonResult.error(403,"无权删除已经处理完成的异常");}
        }catch (Exception e)
        {return JsonResult.error(403,"非法请求");}

        Map paramsMap = new HashMap();
        String sql = "DELETE FROM ws_eng_exception_dtl WHERE id=#{id}";
        paramsMap.put("dsqlStr",sql);
        paramsMap.put("id",id);
        Integer updateNum = publicMapper.delItems(paramsMap);
        return JsonResult.success(updateNum);
    }

    @Override
    public JsonResult<Integer> addExceptionEventDtl(Map map) {
        Map paramsMap = new HashMap();

        if (map.get("eid")!=null && !map.get("eid").toString().equals(""))
        {
            String state;
            try {
                HashMap parm = new HashMap();
                parm.put("eid",Integer.parseInt(map.get("eid").toString()));
                List<Map<String, Object>> list = getExceptionEventList(parm);
                if (list.size()>0)
                {
                    state = list.get(0).get("state").toString();
                    if (state.equals("2"))
                    {
                        return JsonResult.error(403,"不可以添加已完成的异常事件处理细则");
                    }
                }
                else
                {return JsonResult.error(404,"异常事件不存在"); }
            }catch (Exception e)
            {return JsonResult.error(403,"非法请求");}

            for (Object key : map.keySet())
            {
                paramsMap.put(key,map.get(key));
            }
        }
        else
        { return JsonResult.error(403,"非法请求！"); }

        String sql = "INSERT INTO ws_eng_exception_dtl VALUES(NULL,#{eid},#{dept},#{content},NOW())";
        paramsMap.put("isqlStr",sql);
        Integer updateNum = publicMapper.saveItems(paramsMap);

        try {
            HashMap map2 = new HashMap();
            map2.put("eid",paramsMap.get("eid"));
            List<Map<String, Object>> list = getExceptionEventList(map2);
            if (list.size()>0 && list.get(0).get("state").toString().equals("0"))
            {
                setEventState(Integer.parseInt(list.get(0).get("eid").toString()),1);
            }

        }catch (Exception exc)
        {exc.printStackTrace();}

        return JsonResult.success(updateNum);
    }

    @Override
    public JsonResult<Integer> editExceptionEventDtl(Map map) {
        Map paramsMap = new HashMap();

        if (map.get("id")==null || map.get("id").toString().equals(""))
        { return JsonResult.error(403,"非法请求"); }

        try {
            String state = getEventByDtlId(Integer.parseInt(map.get("id").toString())).get("state").toString();
            if (state.equals("2"))
            {return JsonResult.error(403,"无权修改已经处理完成的异常");}
        }catch (Exception e)
        {return JsonResult.error(403,"非法请求");}

        for (Object key:map.keySet())
        {
            paramsMap.put(key,map.get(key));
        }

        String sql = "UPDATE ws_eng_exception_dtl SET dept=#{dept},content=#{content} WHERE id=#{id}";
        paramsMap.put("usqlStr", sql);

        return JsonResult.success(publicMapper.updateItems(paramsMap));
    }

    @Override
    public Map<String, Object> getEventByDtlId(Integer id) {
        if (id==null)
        { return null; }
        Map paramsMap = new HashMap();
        paramsMap.put("id",id);

        String sql = "SELECT * FROM ws_eng_exception_event " +
                "WHERE eid=(SELECT eid FROM ws_eng_exception_dtl dtl WHERE dtl.id=#{id})";
        paramsMap.put("sqlStr", sql);
        List<Map<String,Object>> list = publicMapper.getPublicItems(paramsMap);

        if (list.size()>0)
        { return list.get(0); }
        else
        {return null;}
    }

    @Override
    public JsonResult<Integer> setEventState(Integer eid, Integer state) {
        HashMap paramsMap = new HashMap();

        if (eid!=null && state!=null)
        {
            paramsMap.put("state", state);
            paramsMap.put("eid", eid);
            String sql = "UPDATE ws_eng_exception_event SET state=#{state} WHERE eid=#{eid}";
            paramsMap.put("usqlStr", sql);
            if (state==2)
            {
                HashMap parm2 = new HashMap();
                parm2.put("eid", eid);
                List<Map<String,Object>> list = getExceptionEventList(parm2);
                if (list.size()>0)
                {
                    if (list.get(0).get("state").toString().equals("2"))
                    { return JsonResult.error(400,"该异常事件已经标注过处理完成！"); }
                    else
                    {
                        paramsMap.put("usqlStr", "UPDATE ws_eng_exception_event SET state=#{state},finish_time=NOW() WHERE eid=#{eid}");
                        return JsonResult.success(publicMapper.updateItems(paramsMap));
                    }

                }else
                {return JsonResult.error(404,"异常事件不存在！");}
            }
            else
            { return JsonResult.success(publicMapper.updateItems(paramsMap)); }



        }else
        {return JsonResult.error(400,"参数格式错误");}
    }

    public Boolean sendRTXNoti(String username)
    {
        Map paramsMap = new HashMap();

        String sql = "SELECT * FROM ws_employee WHERE emp_name=#{emp_name} AND is_work=1";
        paramsMap.put("emp_name", username);
        paramsMap.put("sqlStr", sql);
        List<Map<String,Object>> list = publicMapper.getPublicItems(paramsMap);
        if (list.size()==1)
        {
            String parent_emp_no = list.get(0).get("parent_emp_no").toString();
            if (parent_emp_no.equals("U000202"))
            {
                RTX.sendMsg("工程协同系统异常事件通知","工程协同系统中有异常事件等待你跟进处理！",username);
                return true;
            }else
            {return false;}
        }else
        {return false;}
    }

    @Override
    public void exportExceptionEventData(HttpServletResponse response, Map map) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = null;
        try {
            fileName = URLEncoder.encode("异常事件汇总表", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        List<ExcelHeader> headerList = new ArrayList<>();

        headerList.add(new ExcelHeader("n", "序号"));

        ExcelHeader dateBackHeader2 = new ExcelHeader("subm_time", "提报时间");
        dateBackHeader2.setDataType(ExcelHeader.DATE);
        dateBackHeader2.setFormat("yyyy-MM-dd");
        headerList.add(dateBackHeader2);
        headerList.add(new ExcelHeader("subm_men", "提报人"));
        headerList.add(new ExcelHeader("machineType", "机型"));
        headerList.add(new ExcelHeader("pro_type", "产品类别"));
        headerList.add(new ExcelHeader("conDept", "事业部"));
        headerList.add(new ExcelHeader("erp", "订单ERP"));
        headerList.add(new ExcelHeader("conBillNo", "订单号"));
        headerList.add(new ExcelHeader("cusSentToName", "发往地"));
        headerList.add(new ExcelHeader("problem_description", "问题描述"));
        headerList.add(new ExcelHeader("exception_type_name", "问题属性"));
        headerList.add(new ExcelHeader("analysis_result", "工程分析"));
        headerList.add(new ExcelHeader("temp_disposal", "临时方案"));
        headerList.add(new ExcelHeader("dept_name", "责任部门"));
        headerList.add(new ExcelHeader("follow_up_people", "责任人"));

        headerList.add(new ExcelHeader("exp_proce_results", "长期方案"));
        headerList.add(new ExcelHeader("state", "改善进度"));
        headerList.add(new ExcelHeader("isclose", "是否关闭"));

        List<Map<String, Object>> dataList = getExceptionEventList(map);

        for(int k=0;k<dataList.size();k++)
        {
            Map<String, Object> m = dataList.get(k);
            m.put("n",String.valueOf(k+1));
            m.put("isclose","否");

            if (m.get("state").toString().equals("0"))
            {m.put("state","等待处理");}
            else if (m.get("state").toString().equals("1"))
            {m.put("state","正在处理");}
            else if(m.get("state").toString().equals("2"))
            {m.put("state","处理完成");m.put("isclose","是");}
            else
            {m.put("state","未知状态");}

        }

        OutputStream excelOutputStream = EasyExcelUtils.exportDataToExcel(headerList, dataList);
        try {
            response.getOutputStream().write(((ByteArrayOutputStream) excelOutputStream).toByteArray());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public JsonResult<Map> getExceptionTypeStatisticalResult(Map map) {
        HashMap paramsMap = new HashMap();
        String sqltmp="";
        if (map.containsKey("Statistical_time_start") && (!map.get("Statistical_time_start").toString().equals("")) && map.containsKey("Statistical_time_end") && (!map.get("Statistical_time_end").toString().equals(""))) {
            String Statistical_time_start = map.get("Statistical_time_start").toString();
            String Statistical_time_end = map.get("Statistical_time_end").toString();
            paramsMap.put("Statistical_time_start", Statistical_time_start);
            paramsMap.put("Statistical_time_end", Statistical_time_end);
            sqltmp = "WHERE subm_time BETWEEN #{Statistical_time_start} AND #{Statistical_time_end}";
        }

        String sql = "SELECT exception_type_id,c.exception_type_name,COUNT(*) as totals FROM ws_eng_exception_event a " +
                "JOIN ws_eng_exception_dept b ON a.resp_dept=b.id " +
                "JOIN ws_eng_exception_type c ON a.exception_type_id=c.id " +sqltmp+
                " GROUP BY exception_type_id";
        paramsMap.put("sqlStr", sql);
        List<Map<String,Object>> list = publicMapper.getPublicItems(paramsMap);

        if (list.size()>0)
        {
            List<String> legend = new ArrayList<String>();
            List<Map<String,Object>> valueData = new ArrayList<Map<String,Object>>();
            List<Integer> ints=new ArrayList<Integer>();
            for (Map<String,Object> m:list)
            {
                legend.add(m.get("exception_type_name").toString());
                ints.add(Integer.parseInt(m.get("totals").toString()));

                Map<String,Object> m2 = new HashMap<String,Object>();
                m2.put("value",Integer.parseInt(m.get("totals").toString()));
                m2.put("name",m.get("exception_type_name").toString());
                valueData.add(m2);
            }

            sql = "SELECT wide_type,COUNT(*) as totals FROM ws_eng_exception_event " + sqltmp+
                    " GROUP BY wide_type";
            paramsMap.put("sqlStr", sql);
            List<Map<String,Object>> list2 = publicMapper.getPublicItems(paramsMap);
            List<Map<String,Object>> wideTypevalueData = new ArrayList<Map<String,Object>>();
            for (Map<String,Object> k:list2)
            {
                Map<String,Object> m2 = new HashMap<String,Object>();
                m2.put("value",Integer.parseInt(k.get("totals").toString()));
                m2.put("name",k.get("wide_type").toString().equals("0")?"订单资料异常":"机型履历异常");
                wideTypevalueData.add(m2);
            }
            Map<String,Object> resMap = new HashMap<String,Object>();
            resMap.put("ints",ints);
            resMap.put("legend",legend);
            resMap.put("valueData",valueData);
            resMap.put("wideTypevalueData",wideTypevalueData);
            return JsonResult.success(resMap);
        }
        else
        {return JsonResult.error(403,"未查询到结果！");}
    }

    @Override
    public JsonResult<Map> getDeptStatisticalResult(Map map) {
        HashMap paramsMap = new HashMap();
        String sqltmp="";
        if (map.containsKey("Statistical_time_start") && (!map.get("Statistical_time_start").toString().equals("")) && map.containsKey("Statistical_time_end") && (!map.get("Statistical_time_end").toString().equals(""))) {
            String Statistical_time_start = map.get("Statistical_time_start").toString();
            String Statistical_time_end = map.get("Statistical_time_end").toString();
            paramsMap.put("Statistical_time_start", Statistical_time_start);
            paramsMap.put("Statistical_time_end", Statistical_time_end);
            sqltmp = "WHERE subm_time BETWEEN #{Statistical_time_start} AND #{Statistical_time_end}";
        }

        String sql = "SELECT resp_dept,b.dept_name,COUNT(*) as totals FROM ws_eng_exception_event a " +
                "JOIN ws_eng_exception_dept b ON a.resp_dept=b.id " +
                "JOIN ws_eng_exception_type c ON a.exception_type_id=c.id " +sqltmp+
                " GROUP BY resp_dept";
        paramsMap.put("sqlStr", sql);
        List<Map<String,Object>> list = publicMapper.getPublicItems(paramsMap);

        if (list.size()>0)
        {
            List<Map<String,Object>> valueData = new ArrayList<Map<String,Object>>();
            for (Map<String,Object> m:list)
            {
                Map<String,Object> m2 = new HashMap<String,Object>();
                m2.put("value",Integer.parseInt(m.get("totals").toString()));
                m2.put("name",m.get("dept_name").toString());
                valueData.add(m2);
            }
            Map<String,Object> resMap = new HashMap<String,Object>();
            resMap.put("valueData",valueData);
            return JsonResult.success(resMap);
        }
        else
        {return JsonResult.error(403,"未查询到结果！");}
    }
}
